Mega Code Archive
Date Time Functions
1) %%
2) %a
3) %b
4) %c
5) %D
6) %e
7) %f
8) %H
9) %I
10) %j
11) %k
12) %l
13) %M
14) %p
15) %r
16) %S
17) %T
18) %U
19) %V
20) %W
21) %X
22) %Y
23) ADDDATE(date,INTERVAL expr unit)
24) ADDDATE(expr,days)
25) Adding one day to a date
26) Adding one month to a date
27) Adding one month to start date
28) Adding one year to a date
29) ADDTIME(01
30) ADDTIME(expr1,expr2)
31) An illegal date returns a NULL result
32) Changing Date Values
33) Combine Month function and Date_add function
34) CONVERT_TZ(dt,from_tz,to_tz)
35) CURDATE()
36) Current Date and Time
37) CURRENT_TIMESTAMP() is the synonym for NOW()
38) CURTIME() returns the current time as a value in HH
39) Date and Time Functions
40) Date arithmetic also can be performed using INTERVAL together with the + or - operator
41) Date arithmetic operations require complete dates
42) DATE(expr) Extracts the date part of the date or datetime expression expr
43) DATE_ADD(curdate(), INTERVAL 1
44) DATE_ADD(curdate(), INTERVAL 1 1
45) DATE_ADD(curdate(), INTERVAL 1 1 DAY_HOUR);(wihtout
46) DATE_ADD(curdate(), INTERVAL 1 1 DAY_MICROSECOND)
47) DATE_ADD(curdate(), INTERVAL 1 1 HOUR_MICROSECOND)
48) DATE_ADD(curdate(), INTERVAL 1 1 HOUR_MINUTE); (with dot)
49) DATE_ADD(curdate(), INTERVAL 1 1 HOUR_SECOND)
50) DATE_ADD(curdate(), INTERVAL 1 1 MINUTE_MICROSECOND)
51) DATE_ADD(curdate(), INTERVAL 1 1 MINUTE_SECOND)
52) DATE_ADD(curdate(), INTERVAL 1 1 SECOND)
53) DATE_ADD(curdate(), INTERVAL 1 1 SECOND_MICROSECOND)
54) DATE_ADD(curdate(), INTERVAL 1 1 SECOND_MICROSECOND); (with dot)
55) DATE_ADD(curdate(), INTERVAL 1 1 YEAR_MONTH); (with dot)
56) DATE_ADD(curdate(), INTERVAL 1 DAY)
57) DATE_ADD(curdate(), INTERVAL 1 HOUR)
58) DATE_ADD(curdate(), INTERVAL 1 MICROSECOND)
59) DATE_ADD(curdate(), INTERVAL 1 MINUTE)
60) DATE_ADD(curdate(), INTERVAL 1 MONTH)
61) DATE_ADD(curdate(), INTERVAL 1 QUARTER)
62) DATE_ADD(curdate(), INTERVAL 1 SECOND)
63) DATE_ADD(curdate(), INTERVAL 1 WEEK)
64) DATE_ADD(curdate(), INTERVAL 1 YEAR)
65) DATE_ADD(curdate(), INTERVAL 1-1 YEAR_MONTH); (with minus sign)
66) DATE_ADD(date,INTERVAL expr unit)
67) DATE_FORMAT()
68) DATE_SUB(curdate(), INTERVAL 1 1 MINUTE_MICROSECOND)
69) DATE_SUB(curdate(), INTERVAL 1 1 MINUTE_SECOND)
70) DATE_SUB(curdate(), INTERVAL 1 1 SECOND)
71) DATE_SUB(curdate(), INTERVAL 1 1 SECOND_MICROSECOND)
72) DATE_SUB(curdate(), INTERVAL 1 DAY)
73) DATE_SUB(curdate(), INTERVAL 1 HOUR)
74) DATE_SUB(curdate(), INTERVAL 1 MICROSECOND)
75) DATE_SUB(curdate(), INTERVAL 1 MINUTE)
76) DATE_SUB(curdate(), INTERVAL 1 MONTH)
77) DATE_SUB(curdate(), INTERVAL 1 QUARTER)
78) DATE_SUB(curdate(), INTERVAL 1 SECOND)
79) DATE_SUB(curdate(), INTERVAL 1 WEEK)
80) DATE_SUB(curdate(), INTERVAL 1 YEAR)
81) DATE_SUB(date,INTERVAL expr unit)
82) DATEDIFF(expr1,expr2)
83) Dates can also be formatted in plain english
84) DAYNAME(2006-05-00)
85) DAYNAME(date) returns the name of the weekday for date
86) DAYOFMONTH(2001-11-00)
87) DAYOFMONTH(date) returns the day of the month for a given date
88) DAYOFMONTH(date) returns the day of the month for date, in the range 0 to 31
89) DAYOFWEEK(date) returns the day of the week for a given date, starting with 1=Sunday, 2=Monday, 3=Tuesday, and so on.t
90) DAYOFWEEK(date) returns the weekday index for date (1 = Sunday, 2 = Monday, , 7 = Saturday)
91) DAYOFYEAR(date) returns the day of the year for date, in the range 1 to 366
92) Days Since 1 A D
93) Deal with two-digit-year value
94) EXTRACT(unit FROM date)
95) Extracting Years, Quarters, Months, and Weeks
96) Extraction Functions
97) FROM_DAYS(N)
98) FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)
99) Get DAYNAME of employees start date
100) Get DAYOFMONTH of start date
101) Get Month name of the employees start date
102) GET_FORMAT(DATE,EUR)
103) GET_FORMAT(DATE,INTERNAL)
104) GET_FORMAT(DATE,ISO)
105) GET_FORMAT(DATE,JIS)
106) GET_FORMAT(DATE,USA)
107) GET_FORMAT(DATETIME,EUR)
108) GET_FORMAT(DATETIME,INTERNAL)
109) GET_FORMAT(DATETIME,ISO)
110) GET_FORMAT(DATETIME,JIS)
111) GET_FORMAT(DATETIME,USA)
112) GET_FORMAT(DATETIMEDATETIME, EURUSAJISISOINTERNAL)
113) GET_FORMAT(TIME,EUR)
114) GET_FORMAT(TIME,INTERNAL)
115) GET_FORMAT(TIME,ISO)
116) GET_FORMAT(TIME,JIS)
117) GET_FORMAT(TIME,USA)
118) Getting a date 6 months in the past
119) Getting Day Information
120) Getting Names for Months and Days
121) Getting the Current Date and Time
122) HOUR(time) returns the hour for time
123) If the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maxi
124) If you add to or subtract from a date value something that contains a time part, the result is automatically converted
125) LAST_DAY(date) returns the corresponding value for the last day of the month
126) LOCALTIME() is the synonym for NOW()
127) LOCALTIMESTAMP is the synonym for NOW()
128) MAKEDATE(year,dayofyear) returns a date, given year and day-of-year values
129) MAKETIME(hour,minute,second) returns a time value calculated from the hour, minute, and second arguments
130) MICROSECOND(expr) returns the microseconds as a number in the range from 0 to 999999
131) MINUTE(time) returns the minute for time, in the range 0 to 59
132) MONTH(2005-00-00)
133) MONTH(date) returns the month for date, in the range 0 to 12
134) MONTHNAME(date) returns the full name of the month for date
135) Multiple references to a function such as NOW() within a single query always produce the same result
136) Pass DAYOFYEAR a date as a string parameter
137) PERIOD_ADD(P,N) adds N months to period P (in the format YYMM or YYYYMM)
138) PERIOD_DIFF(P1,P2) returns the number of months between periods P1 and P2
139) QUARTER(date) returns the quarter of the year for date, in the range 1 to 4
140) QUARTER(date) the range 1 to 4
141) SEC_TO_TIME(seconds) returns the seconds argument, converted to hours, minutes, and seconds
142) SECOND(time) returns the second for time, in the range 0 to 59
143) Seconds Since the Beginning of the Day
144) SELECT 1998-01-01 - INTERVAL 1 SECOND
145) SELECT 2005-03-32 + INTERVAL 1 MONTH
146) SELECT CONVERT_TZ(2004-01-01 12
147) SELECT DATE_ADD(1992-12-31 23
148) SELECT DATE_ADD(1997-12-31 23
149) SELECT DATE_ADD(1998-01-01 00
150) SELECT DATE_ADD(1999-01-01, INTERVAL 1 HOUR)
151) SELECT DATE_FORMAT(1997-10-04 22
152) SELECT DATE_FORMAT(1999-01-01, %X %V)
153) SELECT DATE_FORMAT(2003-10-03,GET_FORMAT(DATE,EUR))
154) SELECT DATE_FORMAT(2006-06-00, %d)
155) SELECT DATE_SUB(1998-01-01 00
156) SELECT DATE_SUB(1998-01-02, INTERVAL 31 DAY)
157) Select DATE_SUB(curdate(), INTERVAL 1
158) Select DATE_SUB(curdate(), INTERVAL 1 1
159) Select DATE_SUB(curdate(), INTERVAL 1 1 DAY_HOUR)
160) Select DATE_SUB(curdate(), INTERVAL 1 1 DAY_MICROSECOND)
161) Select DATE_SUB(curdate(), INTERVAL 1 1 HOUR_MICROSECOND)
162) Select DATE_SUB(curdate(), INTERVAL 1 1 HOUR_MINUTE); (with dot)
163) Select DATE_SUB(curdate(), INTERVAL 1 1 HOUR_SECOND)
164) Select DATE_SUB(curdate(), INTERVAL 1 1 SECOND_MICROSECOND)
165) Select DATE_SUB(curdate(), INTERVAL 1 1 YEAR_MONTH); (with dot)
166) Select DATE_SUB(curdate(), INTERVAL 1-1 YEAR_MONTH)
167) SELECT DAYNAME(2000-01-01)
168) SELECT EXTRACT(DAY FROM 2003-01-02 10
169) SELECT EXTRACT(DAY_HOUR FROM 2003-01-02 10
170) SELECT EXTRACT(DAY_MICROSECOND FROM 2003-01-02 10
171) SELECT EXTRACT(DAY_MINUTE FROM 1999-07-02 01
172) SELECT EXTRACT(DAY_SECOND FROM 2003-01-02 10
173) SELECT EXTRACT(HOUR FROM 2003-01-02 10
174) SELECT EXTRACT(HOUR_MICROSECOND FROM 2003-01-02 10
175) SELECT EXTRACT(HOUR_MINUTE FROM 2003-01-02 10
176) SELECT EXTRACT(HOUR_SECOND FROM 2003-01-02 10
177) SELECT EXTRACT(MICROSECOND FROM 2003-01-02 10
178) SELECT EXTRACT(MINUTE FROM 2003-01-02 10
179) SELECT EXTRACT(MINUTE_MICROSECOND FROM 2003-01-02 10
180) SELECT EXTRACT(MINUTE_SECOND FROM 2003-01-02 10
181) SELECT EXTRACT(MONTH FROM 2003-01-02 10
182) SELECT EXTRACT(QUARTER FROM 2003-01-02 10
183) SELECT EXTRACT(SECOND FROM 2003-01-02 10
184) SELECT EXTRACT(SECOND_MICROSECOND FROM 2003-01-02 10
185) SELECT EXTRACT(WEEK FROM 2003-01-02 10
186) SELECT EXTRACT(YEAR FROM 1999-07-02)
187) SELECT EXTRACT(YEAR_MONTH FROM 1999-07-02 01
188) SELECT FROM_UNIXTIME(1111885200)
189) SELECT INTERVAL 1 DAY + 1997-12-31
190) SELECT LAST_DAY(2003-03-32)
191) SELECT LAST_DAY(2004-01-01 01
192) SELECT LAST_DAY(2004-02-05)
193) SELECT MAKEDATE(2001,0)
194) Select MAKEDATE(2001,32)
195) SELECT MAKEDATE(2001,365)
196) Select MAKEDATE(2004,365)
197) SELECT MICROSECOND(1997-12-31 23
198) SELECT MID(YEARWEEK(2000-01-01),5,2)
199) SELECT MONTHNAME(1989-07-20)
200) SELECT NOW(), SLEEP(2), NOW()
201) SELECT STR_TO_DATE(00000000, %m%d%Y)
202) SELECT STR_TO_DATE(04312004, %m%d%Y)
203) SELECT STR_TO_DATE(10 31 2003,GET_FORMAT(DATE,USA))
204) SELECT SUBDATE(1998-01-02, INTERVAL 31 DAY)
205) SELECT SUBTIME(01
206) SELECT SYSDATE(), SLEEP(2), SYSDATE()
207) SELECT TIME(2003-12-31 01
208) SELECT TIME_TO_SEC(00
209) SELECT TIMEDIFF(1997-12-31 23
210) SELECT TIMESTAMPADD(DAY,1,2003-01-02)
211) SELECT TIMESTAMPADD(FRAC_SECOND,1,2003-01-02)
212) SELECT TIMESTAMPADD(HOUR,1,2003-01-02)
213) SELECT TIMESTAMPADD(MONTH,1,2003-01-02)
214) SELECT TIMESTAMPADD(QUARTER,1,2003-01-02)
215) SELECT TIMESTAMPADD(SECOND,1,2003-01-02)
216) SELECT TIMESTAMPADD(WEEK,1,2003-01-02)
217) SELECT TIMESTAMPADD(YEAR,1,2003-01-02)
218) SELECT TIMESTAMPDIFF(DAY,2003-02-01,2003-05-01)
219) SELECT TIMESTAMPDIFF(FRAC_SECOND,2003-02-01,2003-05-01)
220) SELECT TIMESTAMPDIFF(HOUR,2003-02-01,2003-05-01)
221) SELECT TIMESTAMPDIFF(MINUTE,2003-02-01,2003-05-01)
222) SELECT TIMESTAMPDIFF(MONTH,2003-02-01,2003-05-01)
223) SELECT TIMESTAMPDIFF(QUARTER,2003-02-01,2003-05-01)
224) SELECT TIMESTAMPDIFF(SECOND,2003-02-01,2003-05-01)
225) SELECT TIMESTAMPDIFF(WEEK,2003-02-01,2003-05-01)
226) SELECT TIMESTAMPDIFF(YEAR,2002-05-01,2001-01-01)
227) SELECT TIMESTAMPDIFF(YEAR,2003-02-01,2003-05-01)
228) SELECT TO_DAYS(1997-10-07)
229) SELECT UNIX_TIMESTAMP(2005-03-27 03
230) SELECT WEEKDAY(1997-11-05)
231) Select YEAR(1973-07-20)
232) SELECT YEARWEEK(2000-01-01)
233) Selects all rows with a start_date value from within the last 30 days
234) STR_TO_DATE(str,format)
235) SUBDATE(date,INTERVAL expr unit)
236) SUBDATE(expr,days)
237) SUBTIME(expr1,expr2)
238) SYSDATE() returns the current date and time
239) TIME(expr) extracts the time part of the time or datetime expression expr and returns it as a string
240) TIME_FORMAT(time,format)
241) TIME_TO_SEC(time) returns the time argument, converted to seconds
242) TIMEDIFF(expr1,expr2) returns expr1 C expr2 expressed as a time value
243) TIMESTAMP(expr) returns the date or datetime expression expr as a datetime value
244) TIMESTAMP(expr1,expr2) adds the time expression expr2 to the date or datetime expression expr1 and returns the result
245) TIMESTAMPADD(unit,interval,datetime_expr) adds the integer expression interval to datetime_expr
246) TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
247) To convert a year-week to a date, then you should also specify the weekday
248) TO_DAYS(date) returns a day number (the number of days since year 0)
249) UNIX_TIMESTAMP() returns a Unix timestamp (seconds since 1970-01-01 00
250) UNIX_TIMESTAMP(date) returns the value of the argument as seconds since 1970-01-01 00
251) Use month function in where clause
252) Use month function with data in a table
253) Using CURDATE() function
254) Using CURTIME() function
255) Using DATE_FORMAT() with an alias to tidy up the title
256) Using DATE_SUB function to substract 55 years from current date
257) Using DATEDIFF with data in a column
258) UTC_DATE() returns the current UTC date
259) UTC_TIME() returns the current UTC time
260) UTC_TIMESTAMP() returns the current UTC date and time
261) WEEK(date), parameter, returns the week of the year in the range 0 to 53
262) WEEK(date[,mode]) returns the week number for date
263) WEEKDAY(date) different and returns the weekday index for the given date(0=Monday, 1=Tuesday, 2=Wednesday, and so on).
264) WEEKDAY(date) returns the weekday index for date (0 = Monday, 1 = Tuesday, 6 = Sunday)
265) WEEKOFYEAR(date) returns the calendar week of the date as a number in the range from 1 to 53
266) YEAR(date) returns the 4-digit numeric year of a given date
267) YEAR(date) returns the year for date, in the range 1000 to 9999, or 0 for the zero date
268) YEARWEEK(date), YEARWEEK(date,mode) returns year and week for a date
269) You can optionally give FROM_UNIXTIME function a format parameter